/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.elcom.emocbc.dbmapping.persistence;

import com.elcom.emocbc.dbmapping.entities.CustomerSubscribedPackage;
import com.elcom.emocbc.dbmapping.tables.pojos.MtpkgPackageDetail;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

/**
 *
 * @author GiangLT
 */
public class MtpkgSubscriberPersistance {

    public List<CustomerSubscribedPackage> fetchByMsisdn(String msisdn) throws SQLException {
        ResultSetHandler<List<CustomerSubscribedPackage>> resultSetHandler = new ResultSetHandler<List<CustomerSubscribedPackage>>() {
            @Override
            public List<CustomerSubscribedPackage> handle(ResultSet rs) throws SQLException {
                List<CustomerSubscribedPackage> result = new ArrayList<CustomerSubscribedPackage>();
                while (rs.next()) {
                    CustomerSubscribedPackage subscription = new CustomerSubscribedPackage();
                    int i = 1;
                    subscription.setMsisdn(rs.getString(i++));
                    subscription.setPkgdId(rs.getString(i++));
                    subscription.setRegisterTime(rs.getTimestamp(i++));
                    subscription.setDeregisterTime(rs.getTimestamp(i++));
                    subscription.setStatus(rs.getShort(i++));
                    subscription.setLastUpdate(rs.getTimestamp(i++));
                    subscription.setLastUpdateBy(rs.getString(i++));
                    
                    MtpkgPackageDetail packageDetail = new MtpkgPackageDetail();
                    packageDetail.setPkgdId(rs.getString(i++));
                    packageDetail.setPkgdName(rs.getString(i++));
                    packageDetail.setPkgdType(rs.getString(i++));
                    packageDetail.setPkgId(rs.getInt(i++));
                    packageDetail.setRegisterCode(rs.getString(i++));
                    packageDetail.setDeregisterCode(rs.getString(i++));
                    packageDetail.setActiveFee(rs.getBigDecimal(i++));
                    packageDetail.setRecurrentFee(rs.getBigDecimal(i++));
                    subscription.setMtpkgPackageDetail(packageDetail);
                            
                    result.add(subscription);
                }
                return result;
            }
        };
        Connection con = DbAccess.getInstance().getConnection();

        QueryRunner qr = new QueryRunner();
        String sql = "SELECT s.*, p.* "
                + "FROM mtpkg_subscriber s "
                + "INNER JOIN mtpkg_package_detail p "
                + "     ON p.pkgd_id = s.pkgd_id "
                + "WHERE s.msisdn = ? ";
        try {
            return qr.query(con, sql, resultSetHandler, msisdn);
        } finally {
            DbUtils.closeQuietly(con);
        }
    }
}
